from db.dbUtils import MysqlPool


def get_department_call_by_z_t_dao(z, t):
    sql = """SELECT * FROM `se_at_department` WHERE 
    `week`={} and `day`={}
    """.format(z, t)
    with MysqlPool() as pool:
        cursor = pool.cursor
        cursor.execute(sql)
        return cursor.fetchall()


def get_major_call_by_z_t_dao(z, t, d_id):
    sql = """SELECT * FROM `se_at_major` WHERE 
        `week`={} and `day`={} and `department_id`={}
        """.format(z, t, d_id)
    with MysqlPool() as pool:
        cursor = pool.cursor
        cursor.execute(sql)
        return cursor.fetchall()


def get_grade_call_by_z_t_dao(z, t, m_id):
    sql = """
    select
       count(*) as `grade_all_number`,
       count(if(state = 0, id, null)) as `grade_at_number`,
       count(if(state != 0, id, null)) as 'grade_no_at_number',
       ANY_VALUE(`class_id`) as class_id,
       ANY_VALUE(`major_id`) as major_id
from `se_at_class` where `major_id`={} and `week`={} and `day`={} group by `class_id`;
            """.format(m_id, z, t)
    with MysqlPool() as pool:
        cursor = pool.cursor
        cursor.execute(sql)
        return cursor.fetchall()